﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using MKDataBase.Contracts;

namespace MKDataBase.Implementation
{
    public class SqlDBProvider:IDBProvider
    {

        /// <summary>
        /// 
        /// </summary>
        private static string ConStrName = "MKMoviesConnectionString";

        /// <summary>
        /// Gets the connecton string.
        /// </summary>
        private static string ConnectonString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings[ConStrName].ConnectionString;
            }
        }
        IDBProvider idbprovider;

        public SqlDBProvider()
        {
        }
        #region MovieManager Operations

        private static string AddMovieToMovieManagerSP = "InsertAMovie";
        private static string GetMoviesFromMovieManagerSP = "GetMoviesFromManager";
        private static string VerifyMovieSP = "VerifyMovieIdinManager";

        /// <summary>
        /// Adds the A movie.
        /// </summary>
        /// <param name="row">The row.</param>
        /// <returns></returns>
        public DBStatus AddAMovie(MovieManagerDataRow row)
        {
            SqlConnection connection = new SqlConnection(ConnectonString);
            try
            {
                connection.Open();
                SqlCommand verify = new SqlCommand(VerifyMovieSP, connection);
                verify.CommandType = CommandType.StoredProcedure;
                verify.Parameters.Add(new SqlParameter(@"MovieName", row.MovieName));
                var result = verify.ExecuteReader();
                if (result.HasRows)
                    return DBStatus.RowExists;

                SqlCommand command = new SqlCommand(AddMovieToMovieManagerSP, connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(row.MapTo().ToArray());
                var re = command.ExecuteNonQuery();
            }
            catch (Exception ex) { }
            finally
            {
                connection.Close();
            }
            return DBStatus.Success;
        }

        /// <summary>
        /// Gets the movies.
        /// </summary>
        /// <returns></returns>
        public List<MovieManagerDataRow> GetMovies()
        {
            var movies = new List<MovieManagerDataRow>();
            SqlConnection connection = new SqlConnection(ConnectonString);
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand(GetMoviesFromMovieManagerSP, connection);
                command.CommandType = CommandType.StoredProcedure;
                var re = command.ExecuteReader();
                while (re.Read())
                {
                    var moviename = re["MovieName"].ToString();
                    var id = Convert.ToInt32(re["MovieID"]);
                    movies.Add(new MovieManagerDataRow() { MovieName = moviename, MovieID = id });
                }
            }
            catch (Exception ex) { }
            finally
            {
                connection.Close();
            }

            return movies;
        }
        #endregion

        #region MovieDetails Operations
        private static string AddMovieDetailsSP = "AddMovieDetails";
        private static string VerifyMovieinMgrSP = "VerifyMovieIdinDetails";
        private static string GetMovieDetails = "GetMovieDetails";

        /// <summary>
        /// Adds the movie details.
        /// </summary>
        /// <param name="row">The row.</param>
        /// <returns></returns>
        public DBStatus AddMovieDetails(MovieDetailsDataRow row)
        {
            SqlConnection connection = new SqlConnection(ConnectonString);
            try
            {
                connection.Open();
                SqlCommand verify = new SqlCommand(VerifyMovieinMgrSP, connection);
                verify.CommandType = CommandType.StoredProcedure;
                verify.Parameters.Add(new SqlParameter(@"MovieID", row.ID));
                var result = verify.ExecuteReader();
                if (result.HasRows)
                    return DBStatus.RowExists;
                connection.Close();
                connection.Open();
                SqlCommand command = new SqlCommand(AddMovieDetailsSP, connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(row.MapTo().ToArray());
                var re = command.ExecuteNonQuery();
            }
            catch (Exception ex) { }
            finally
            {
                connection.Close();
            }
            return DBStatus.Success;
        }

        /// <summary>
        /// Gets the movie details by id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public MovieDetailsDataRow GetMovieDetailsById(int id)
        {
            SqlConnection connection = new SqlConnection(ConnectonString);
            try
            {
                connection.Open();
                SqlCommand verify = new SqlCommand(GetMovieDetails, connection);
                verify.CommandType = CommandType.StoredProcedure;
                verify.Parameters.Add(new SqlParameter(@"MovieID", id));
                var result = verify.ExecuteReader();
                if (result.Read())
                {
                    
                    return MapToMovieDetails(result);
                }
            }
            catch (Exception ex) { }
            finally
            {
                connection.Close();
            }
            return null;
        }

        #endregion


        public MovieDetailsDataRow MapToMovieDetails(object obj)
        {
            var result = obj as SqlDataReader;
            if (result.Equals(null))
                return null;
            MovieDetailsDataRow row = new MovieDetailsDataRow()
            {
                ID = Convert.ToInt32(result["MovieID"]),
                Title = result["Title"].ToString(),
                IMDBId = result["IMDBId"].ToString(),
                IMDBUrl = result["IMDBUrl"].ToString(),
                Genre = result["Genre"].ToString(),
                Releasedate = Convert.ToDateTime(result["ReleaseDate"]),
                Rating = Convert.ToSingle(result["Rating"]),
                TagLine = result["TagLine"].ToString(),
                StoryLine = result["StoryLine"].ToString(),
                MovieImage = (byte[])result["Image"]
            };
            return row;
        }


        public bool DeleteMovie(string MovieName, int ID)
        {
            throw new NotImplementedException();
        }


        public DBStatus UpDateMovie(MovieManagerDataRow row)
        {
            throw new NotImplementedException();
        }

        public DBStatus UpdateMovieDetails(MovieDetailsDataRow row)
        {
            throw new NotImplementedException();
        }

        public IDBProvider CurrentDB
        {
            get
            {
                if (idbprovider == null)
                    idbprovider = new SqlDBProvider();
                return idbprovider;
            }
        }

        private static IDBProvider idb;
        public static IDBProvider GetInstance
        {
            get
            {
                if(idb==null)
                    idb=new SqlDBProvider();
                return idb;
            }
        }
    }

}
